﻿namespace etracks.Web.Controllers
{
    using etracks.EntityModels;
    using etracks.Helpers;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.Web.Mvc.Ajax;
    using System.Data.Linq;
    using System.Data.Common;

	[Authorize]
	public class CourseController : BaseController
	{
		public ActionResult Index ()
		{
            CourseIndexViewModel vm = new CourseIndexViewModel();

            vm.Courses = CourseController.GetCourseCount();

			return View (vm);
		}

        public ActionResult List()
        {
            CourseListViewModel vm = new CourseListViewModel();

            vm.CourseList = CourseController.GetCourseList();

            return this.List(vm);
        }

        private ActionResult List(CourseListViewModel vm)
        {
            return View(vm);
        }

        public ActionResult Search()
        {
            return RedirectToAction("List");
        }

        [HttpPost]
        public ActionResult Search(string search_term)
        {
            CourseSearchViewModel vm = new CourseSearchViewModel();

            vm.Search_Term = search_term;

            vm.CourseList = CourseController.CourseSearchResultsList(search_term);

            return View(vm);
        }

        public ActionResult Registration()
        {
            return View(new etracks.Web.CourseRegistrationViewModel());
        }

        [HttpPost]
        public ActionResult Registration(CourseRegistrationViewModel vm)
        {
            // Validate vm
            if (string.IsNullOrEmpty(vm.Name))
                ModelState.AddModelError("Name", "Cannot be empty.");

            if (ModelState.IsValid)
            {
                // Add student into list database records.
                string new_id = RegisterCourse(vm.Name);
            }

            return View(vm);
        }

        ////////////////////////////////////////////////////////////////////////////////
        // Data retrieval hack functions
        private static int GetCourseCount()
        {
            int result = 0;
            object db_result = PostgresHelper.GetObject("SELECT COUNT(*) FROM course;");

            if (db_result != null)
                int.TryParse(db_result.ToString(), out result);
            
            return result;
        }

        private static List<Course> GetCourseList()
        {
            List<Course> result = new List<Course>();

            DataTable dt = PostgresHelper.GetDataTable("SELECT * FROM course ORDER BY id;");

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Course
                {
                    Id = n.Field<string>("id"),
                    Name = n.Field<string>("name")
                }).ToList();
            }

            return result;
        }

        private static List<Course> CourseSearchResultsList(string search_term)
        {
            List<Course> result = new List<Course>();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            
            parameters.Add("search_term", string.Format("%{0}%", search_term));

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT * FROM course 
WHERE 
lower(id) LIKE lower(:search_term)
OR lower(name) LIKE lower(:search_term)
ORDER BY id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Course
                {
                    Id = n.Field<string>("id"),
                    Name = n.Field<string>("name")
                }).ToList();
            }

            return result;
        }

        private static string RegisterCourse(string name)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("name", name);
            
            string sql = "INSERT INTO course (name) VALUES (:name) RETURNING id;";

            object db_result = PostgresHelper.GetObject(sql, parameters);

            if (db_result != null)
                return db_result.ToString();
            else
                return null;

        }
	}
}